<?php

/**
 * 数据库通用类
 * @version 1.0.1
 * @since 2017-8-23 Added.
 * @since 2017-12-15 增加导出方法和解析服务器信息的方法
 */
class Db
{
    /**
     * 数据库连接DSN
     * @var type
     */
    protected $dsn = '';
    /**
     * PDO 数据库连接对象
     * @var \PDO
     */
    protected $link = null;
    /**
     * 数据库环境信息, 数据库名称，数据库帐号等
     * host,database,user,password,port,charset
     * @var array
     */
    protected $config = [
        // 数据库类型，支持  mysql, sqlite 等
        'type'            => '',
        // 服务器地址
        'host'        => '',
        // 数据库名
        'database'        => '',
        // 用户名
        'user'        => '',
        // 密码
        'password'        => '',
        // 端口
        'port'        => '',
        // 连接dsn
        'dsn'             => '',
        // 数据库连接参数
        'params'          => [],
        // 数据库编码默认采用utf8
        'charset'         => 'utf8',
    ];

    /**
     * 数据库连接失败不会主动抛出异常，需要手动获取异常对象
     */
    protected $connectionException = null;

    /**
     * 最后执行的异常信息
     */
    protected $lastException = null;

    /**
     * 当前SQL指令
     * @var string
     */
    protected $queryStr = '';

    /**
     * 事务指令数
     * @var int
     */
    protected $transTimes = 0;

    /**
     * 查询开始时间
     * @var float
     */
    protected $queryStartTime;


    /**
     * 构造方法
     * @param array $dbcfg
     * @throws PDOException
     */
    public function __construct(array $dbcfg)
    {
        if (!isset($dbcfg['password'])) {
            return;
        }
        $this->config = $dbcfg;
    }
    /**
     * 连接数据库
     */
    public function connect($dbcfg = [])
    {
        if (!$dbcfg) {
            $dbcfg = $this->config;
        }
        $this->dsn = $this->getDSN($dbcfg);
        if (empty($this->dsn)) {
            throw new \PDOException('数据库配置文件无效!');
        }
        try {
            $this->link = new \PDO($this->dsn, $dbcfg['user'], $dbcfg['password']);
            $this->link->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION); // 提高错误级别
            $this->connectionException = null;
        } catch (\PDOException $ex) {
            if (false !== strpos($ex->getMessage(), 'Unknown database')) {
                // 如果是数据库未创建而出错，则尝试去掉数据库参数再连接一次
                unset($dbcfg['database']);
                try {
                    $this->connect($dbcfg);
                    $this->connectionException = null;
                } catch (\PDOException $ex2) {
                    $this->connectionException = $ex2;
                    throw $ex2;
                }
            } else {
                $this->connectionException = $ex;
                throw $ex;
            }
            // 命令行环境下错误消息显示不出来的解决方法：
            // 设置CMD环境编码为GBK:936,utf-8:65001，否则PDO实例化异常消息会乱码
            // exec("CHCP 936");
            // 浏览器环境下错误消息显示不出来的解决方法：
            // header("Content-Type:text/html;charset=utf-8");
            // 通用解决方法(直接转码)：
            // echo iconv('gbk', 'utf-8', $ex->getMessage());
            // $message = '数据库连接失败：请检查数据库是否可用或数据库连接信息是否正确 [' . $this->dsn . ']';
            // $message = '数据库连接失败：' . iconv('gbk', 'utf-8', $ex->getMessage());
        }
    }
    public function __destruct()
    {
        $this->dispose();
    }
    public function dispose()
    {
        unset($this->config);
        unset($this->link);
        unset($this->queryOptions);
        unset($this->connectionException);
        unset($this->lastException);
    }
    public function getDSN(array $server)
    {
        // 解析host和port是否有端口转发
        $host = $server['host'];
        $port = $server['port'];
        // 数据库类型
        $adapter = 'mysql';
        if (isset($server['adapter'])) {
            $adapter = $server['adapter'];
        }
        if (isset($server['type'])) {
            $adapter = $server['type'];
        }
        switch ($adapter) {
            case 'mysql':
                $dsnarr = ['host' => $host, 'port' => $port];
                if (isset($server['charset'])) {
                    $dsnarr['charset'] = $server['charset'];
                }
                if (!empty($server['database'])) {
                    $dsnarr['dbname'] = $server['database'];
                }
                $dsn = '';
                foreach ($dsnarr as $name => $value) {
                    $dsn .= $name . '=' . $value . ';';
                }
                $dsn = 'mysql:' . $dsn;
                break;
            case 'sqlite':
                $dsn = 'sqlite:' . $server['database'];
                break;
            default:
                $dsn = '';
                break;
        }
        return $dsn;
    }
    /**
     * 数据库连接失败不会主动抛出异常，需要手动获取异常对象
     */
    public function getConnectException()
    {
        return $this->connectionException;
    }
    /**
     * 数据库操作失败不会主动抛出异常，需要手动获取异常对象
     */
    public function getLastException()
    {
        return $this->lastException;
    }
    /**
     * 返回数据库服务器环境变量的值
     * host,database,user,password,port,charset
     * @param string $name 名称
     * @param mixed $defv 默认值
     */
    public function getConfigValue($name, $defv = '')
    {
        if (!$name) {
            return $this->config;
        }
        return isset($this->config[$name]) ? $this->config[$name] : $defv;
    }

    /**
     * 返回数据库名称，判断数据库名称是否是当前数据库,不一致则切换到该数据库
     * @param string $db_name 参数指定的数据库名称
     * @return string 数据库名称
     */
    public function checkDbName($db_name)
    {
        $cfgDbName = isset($this->config['database']) ? $this->config['database'] : '';
        if (!$db_name && $cfgDbName) {
            $db_name = $cfgDbName;
        }
        if (!$db_name) {
            return '';
        }
        // 动态切换数据库
        if ($db_name != $cfgDbName) {
            $this->lastException = null;
            try {
                $this->execute('use ' . $db_name);
            } catch (\PDOException $ex) {
                $this->lastException = $ex;
                exit('checkDbName()方法执行错误：' . $ex->getMessage());
            }
        }
        return $db_name;
    }
    /**
     * 选择数据库
     */
    public function selectDb($db_name)
    {
        if (!$db_name) {
            return;
        }
        $this->execute('use ' . $db_name);
    }
    // ===================================== 数据库事务 ========================================

    /**
     * 执行数据库事务
     * @access public
     * @param  callable $callback 数据操作方法回调
     * @return mixed
     * @throws PDOException
     * @throws \PDOException
     * @throws \Throwable
     */
    public function transaction(callable $callback)
    {
        $this->startTrans();
        try {
            $result = null;
            if (is_callable($callback)) {
                $result = $callback($this);
            }
            $this->commit();
            return $result;
        } catch (\PDOException | \Throwable $ex) {
            $this->rollback();
            throw $ex;
        }
    }

    /**
     * 启动事务
     * @access public
     * @return void
     * @throws \PDOException
     * @throws \PDOException
     */
    public function startTrans(): void
    {
        if (is_null($this->link)) {
            throw new \PDOException('数据库未连接');
        }
        ++$this->transTimes;
        if (1 == $this->transTimes) {
            if (false === $this->link->beginTransaction()) {
                exit('开启事务失败！');
            }
        }
    }

    /**
     * 用于非自动提交状态下面的查询提交
     * @access public
     * @return void
     * @throws \PDOException
     */
    public function commit(): void
    {
        if (is_null($this->link)) {
            throw new \PDOException('数据库未连接');
        }
        if (1 == $this->transTimes) {
            if (false === $this->link->commit()) {
                exit('提交事务失败！');
            }
        }
        --$this->transTimes;
        echo '提交事务嵌套', $this->transTimes, PHP_EOL;
    }

    /**
     * 事务回滚
     * @access public
     * @return void
     * @throws \PDOException
     */
    public function rollback(): void
    {
        if (is_null($this->link)) {
            throw new \PDOException('数据库未连接');
        }
        if (1 == $this->transTimes) {
            if (false === $this->link->rollBack()) {
                exit('回滚事务失败！');
            }
        }
        $this->transTimes = max(0, $this->transTimes - 1);
    }

    /**
     * 执行一条预处理语句
     * 如果执行失败则返回 false，执行成功则返回被影响的行数
     * 注意：这里不能用foreach，参考bindParam陷阱(值参数是引用型变量)：http://www.laruence.com/2012/10/16/2831.html
     * @param string $sqlStr SQL 命令
     * @param array $params 参数值
     */
    public function execute($sql, $params = [])
    {
        if (is_null($this->link)) {
            throw new \PDOException('数据库未连接');
        }
        $this->lastException = null;
        $stmt = $this->link->prepare($sql);
        try {
            if ($params) {
                $stmt->execute($params);
            } else {
                $stmt->execute();
            }
            return $stmt->rowCount();
        } catch (\PDOException $ex) {
            $this->lastException = $ex;
            throw $ex;
        }
    }
    /**
     * 执行一条SQL语句
     * 如果执行失败则返回 false，执行成功则返回被影响的行数
     * @param string $sqlStr
     */
    public function exec($sql)
    {
        if (is_null($this->link)) {
            throw new \PDOException('数据库未连接');
        }
        $this->lastException = null;
        try {
            return $this->link->exec($sql);
        } catch (\PDOException $ex) {
            $this->lastException = $ex;
            throw $ex;
        }
    }

    /**
     * 执行SQL，返回一行记录
     * 执行成功则返回一行记录，执行失败则返回 null
     * 注意：这里不能用foreach，参考bindParam陷阱(值参数是引用型变量)：http://www.laruence.com/2012/10/16/2831.html
     * @param string $sqlStr SQL 命令
     * @param array $params 参数值
     */
    public function find($sql, $params = [])
    {
        if (is_null($this->link)) {
            throw new \PDOException('数据库未连接');
        }
        $this->lastException = null;
        $stmt = $this->link->prepare($sql);
        try {
            if ($params) {
                $stmt->execute($params);
            } else {
                $stmt->execute();
            }
            return $stmt->fetch(\PDO::FETCH_ASSOC);
        } catch (\PDOException $ex) {
            $this->lastException = $ex;
            return null;
        }
    }
    /**
     * 执行SQL，返回字段值
     * 执行成功则返回一个值，执行失败则返回 null
     */
    public function value($sql, $params = [])
    {
        $data = $this->find($sql, $params);
        return is_array($data) ? current($data) : null;
    }

    /**
     * 执行SQL 命令并返回结果集数组
     * 执行成功则返回数组，执行失败则返回空数组
     * 注意：这里不能用foreach，参考bindParam陷阱(值参数是引用型变量)：http://www.laruence.com/2012/10/16/2831.html
     * @param string $sqlStr SQL 命令
     * @param array $params 参数值
     */
    public function query($sql, $params = [], $fetch_style = \PDO::FETCH_ASSOC)
    {
        if (is_null($this->link)) {
            throw new \PDOException('数据库未连接');
        }
        $this->lastException = null;
        $stmt = $this->link->prepare($sql);
        try {
            if ($params) {
                $stmt->execute($params);
            } else {
                $stmt->execute();
            }
            return $stmt->fetchAll($fetch_style);
        } catch (\PDOException $ex) {
            $this->lastException = $ex;
            return [];
        }
    }

    /**
     * 批处理执行SQL语句
     * 批处理的指令都认为是execute操作
     * @param array $sqls SQL批处理指令
     * @param boolean $isexecute 指示方法是执行或查询
     * @param boolean $isrollback 遇到错误是否回滚
     * @return array 错误消息数组
     * @version 1.0 2016-6-15 xcaller Added.
     * @example batchQuery($asql);
     */
    public function batchQuery($sqls = [], $is_execute = false, $is_rollback = false)
    {
        if (is_null($this->link)) {
            throw new \PDOException('数据库未连接');
        }
        if (!$sqls) {
            throw new \PDOException('没有任何命令');
        }
        // 错误消息数组
        $outData = ['status' => 0, 'affected_rows' => 0, 'data' => ['没有任何命令']];
        // 如果参数是单条SQL 命令，则自动封装为数组
        if (!is_array($sqls)) {
            $sqls = array($sqls);
        }
        $outData['data'] = []; // 清空错误消息数组
        $effectRows = 0; // 受影响的行数
        // 自动启动事务支持，提高执行性能        
        $this->link->beginTransaction(); // mysql_query("BEGIN");
        try {
            foreach ($sqls as $sql) {
                if (!trim($sql)) {
                    continue;
                }
                if ($is_execute) {
                    $result = $this->link->exec($sql);
                    $effectRows += $result;
                } else {
                    $result = $this->link->query($sql);
                }
                // 错误处理
                if (false === $result) {
                    // [SQLSTATE error code, Driver-specific error code, Driver-specific error message] | mysql_error();
                    $errorInfo = $this->link->errorInfo();
                    $result['data'][] = $errorInfo[2];
                    // 发生错误自动回滚事务
                    if ($is_rollback) {
                        $this->link->rollBack(); // mysql_query("ROLLBACK");
                    }
                }
            }
            // 提交事务
            $this->link->commit(); // mysql_query("COMMIT"); mysql_query("END");
            $outData['status'] = 1;
            $outData['affected_rows'] = $effectRows;
            return $outData;
        } catch (\PDOException $ex) {
            // 回滚事务
            $this->link->rollBack(); // mysql_query("ROLLBACK"); mysql_query("END");
            $this->lastException = $ex;
            throw $ex;
        }
    }
    /**
     * 返回数据库版本号
     * @return string 数据库版本号
     */
    public function getDbVersion()
    {
        $sql = 'select version() as `version`;';
        $versionInfo = $this->value($sql);
        return $versionInfo ? $versionInfo : '';
    }

    /**
     * 返回数据库服务器状态
     */
    public function getDbStatus()
    {
        $sql = 'show status;';
        return $this->query($sql);
    }
    /**
     * 返回数据库配置变量列表
     * @return array 配置变量列表
     */
    public function getDbVariables()
    {
        $sql = 'show global variables;';
        return $this->query($sql);
    }

    /**
     * 返回所有数据库
     * @param string $db_name 数据库名称
     * @return array SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME
     */
    public function getDatabases($db_name = '', $with_key_name = true)
    {
        $sqlStr = 'select SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME from '
            . 'information_schema.schemata where SCHEMA_NAME';
        if ($db_name) {
            $sqlStr .= ' = ?';
            $params = [$db_name];
        } else {
            $sqlStr .= ' not in (\'mysql\',\'information_schema\',\'performance_schema\');';
            $params = [];
        }
        $sqlStr .= ' order by SCHEMA_NAME';
        $list = $this->query($sqlStr, $params);
        if ($with_key_name && $list) {
            $list = $this->setArrayKeyByName($list, 'SCHEMA_NAME');
        }
        return $list;
    }

    /**
     * 返回数据表元数据
     * @param string $db_name
     * @return array SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME,DATA_LENGTH,TABLE_COUNT,TABLE_ROWS
     */
    public function getDatabaseInfo($db_name = '')
    {
        $db_name = $this->checkDbName($db_name);
        $list = $this->getDatabases($db_name);
        if (is_array($list)) {
            $data = current($list);
            $dbInfo = $this->getDatabaseSize($db_name);
            if ($dbInfo) {
                $data = array_merge($data, $dbInfo);
            }
            return $data;
        }
        return null;
    }
    /**
     * 返回数据库占用空间大小
     */
    public function getDatabaseSize($db_name)
    {
        $sqlStr = "select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as DATA_LENGTH, sum(TABLE_ROWS) as TABLE_ROWS, count(*) as TABLE_COUNT from information_schema.TABLES where table_schema=:database;";
        $list = $this->query($sqlStr, ['database' => $db_name]);
        return $list ? current($list) : [];
    }
    /**
     * 返回所有数据表名称
     */
    public function getTableNames($db_name = '')
    {
        $tables = $this->getTables($db_name, '', true);
        return is_array($tables) ? array_keys($tables) : [];
    }

    /**
     * 返回所有数据表
     * @param string $db_name
     * @param string $table_name
     * @return array TABLE_NAME, TABLE_TYPE, ENGINE, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH, AUTO_INCREMENT, CREATE_TIME, UPDATE_TIME, TABLE_COLLATION, TABLE_COMMENT, CHARACTER_MAXIMUM_LENGTH
     */
    public function getTables($db_name = '', $tableNames = '', $with_key_name = true, $field = '')
    {
        $db_name = $this->checkDbName($db_name);
        if (!$field) {
            $field = 'TABLE_NAME, TABLE_TYPE, ENGINE, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH, AUTO_INCREMENT, CREATE_TIME, UPDATE_TIME, TABLE_COLLATION, TABLE_COMMENT';
        }
        $sqlStr = "select {$field} from information_schema.TABLES where TABLE_SCHEMA=:database AND TABLE_TYPE='BASE TABLE' order by TABLE_NAME";
        $params = ['database' => $db_name];

        $list = $this->query($sqlStr, $params);
        if ($list) {
            $nameField = 'TABLE_NAME';
            $tableNames = is_array($tableNames) ? $tableNames : array_filter(explode(',', $tableNames));
            if ($tableNames) {
                $list2 = [];
                foreach ($list as $row) {
                    if (in_array($row[$nameField], $tableNames)) {
                        $list2[] = $row;
                    }
                }
                $list = $list2;
                unset($list2);
            }
            if ($with_key_name) {
                $list = $this->setArrayKeyByName($list, $nameField);
            }
        }
        return $list;
    }

    /**
     * 返回数据表元数据
     * @param string $db_name
     * @param string $table_name
     * @param string $field
     * @return array TABLE_NAME, TABLE_TYPE, ENGINE, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH, AUTO_INCREMENT, CREATE_TIME, UPDATE_TIME, TABLE_COLLATION, TABLE_COMMENT
     */
    public function getTableInfo($db_name, $table_name, $field = '')
    {
        $db_name = $this->checkDbName($db_name);
        $list = $this->getTables($db_name, $table_name);
        if (is_array($list)) {
            $data = current($list);
            $data['TABLE_COLUMNS'] = $this->getColumnCount($db_name, $table_name);
            if ($field && isset($data[$field])) {
                return $data[$field];
            }
            return $data;
        }
        return null;
    }
    /**
     * 返回数据表记录数量少于指定数量的表集合
     * @param string $db_name
     * @param int $eltcount
     */
    public function getEmptyTables($db_name, $eltcount = 0, $prefix = '')
    {
        $db_name = $this->checkDbName($db_name);
        $list = $this->query('show tables;');
        $tables = [];
        $tableName = '';
        foreach ($list as $row) {
            $tableName = current($row);
            if ($prefix && false === strpos($tableName, $prefix)) {
                continue;
            }
            $count = $this->count($tableName);
            if ($count > $eltcount) {
                continue;
            }
            $tables[$tableName] = $row;
        }
        return $tables;
    }
    /**
     * 判断数据表是否存在
     */
    public function hasTable($table_name, $db_name = '')
    {
        if (is_null($this->link)) {
            throw new \PDOException('数据库未连接');
        }
        $db_name = $this->checkDbName($db_name);
        $sqlStr = 'select count(*) from information_schema.tables where TABLE_SCHEMA=:database AND TABLE_NAME=:table;';
        $stmt = $this->link->prepare($sqlStr);
        $stmt->execute(['database' => $db_name, 'table' => $table_name]);
        return current($stmt->fetch(\PDO::FETCH_ASSOC)) > 0;
    }

    /**
     * 返回所有索引
     * @param string $db_name
     * @param string $table_name
     * @return array Table, Non_unique, Key_name, Seq_in_index, Column_name, Collation, Cardinality, Sub_part, Packed,
     *          Null, Index_type
     */
    public function getIndexes($db_name, $table_name)
    {
        $db_name = $this->checkDbName($db_name);
        // Key_name, Column_name, Index_type, Index_comment, Collation, Cardinality, Non_unique
        $list = $this->query('show index from ' . filter_var($db_name) . '.' . filter_var($table_name));
        return $list;
    }

    /**
     * 返回所有列
     * @param string $db_name
     * @param string $table_name
     * @param boolean $with_key_name
     * @return array COLUMN_NAME, COLUMN_TYPE, COLUMN_DEFAULT, IS_NULLABLE, CHARACTER_SET_NAME, COLLATION_NAME, `PRIVILEGES`, 
     *      EXTRA, COLUMN_COMMENT, ORDINAL_POSITION, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, COLUMN_KEY
     */
    public function getColumns($db_name, $table_name, $with_key_name = true)
    {
        $db_name = $this->checkDbName($db_name);
        $field = 'COLUMN_NAME, COLUMN_TYPE, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, CHARACTER_SET_NAME, COLLATION_NAME, COLUMN_KEY, EXTRA, COLUMN_COMMENT, `PRIVILEGES`';
        $sqlStr = 'select ' . $field . ' from information_schema.columns WHERE TABLE_SCHEMA=:database AND TABLE_NAME=:table order by ORDINAL_POSITION';
        $list = $this->query($sqlStr, ['database' => $db_name, 'table' => $table_name]);
        if ($with_key_name && $list) {
            $list = $this->setArrayKeyByName($list, 'COLUMN_NAME');
        }
        return $list;
    }
    /**
     * 设置数组的键名
     */
    private function setArrayKeyByName(array $list, string $key_field)
    {
        $data = [];
        foreach ($list as $row) {
            $data[$row[$key_field]] = $row;
        }
        return $data;
    }
    /**
     * 返回数据表的列数量
     */
    public function getColumnCount($db_name, $table_name)
    {
        $db_name = $this->checkDbName($db_name);
        $sqlStr = 'select count(*) from information_schema.COLUMNS where TABLE_SCHEMA=:database and table_name =:table';
        $value = $this->value($sqlStr, ['database' => $db_name, 'table' => $table_name]);
        return $value ? $value : 0;
    }
    /**
     * 返回创建数据表的命令
     */
    public function getCreateTableDDL($db_name, $table_name)
    {
        $db_name = $this->checkDbName($db_name);
        $sqlStr = "show create table {$db_name}.{$table_name}";
        $data = $this->find($sqlStr);
        return isset($data['Create Table']) ? $data['Create Table'] : '';
    }
    /**
     * 返回所有视图
     * @param string $db_name
     * @return array TABLE_NAME
     */
    public function getViews($db_name = '', $with_key_name = true)
    {
        $db_name = $this->checkDbName($db_name);
        $sqlStr = 'select TABLE_NAME, VIEW_DEFINITION, DEFINER, CHARACTER_SET_CLIENT, COLLATION_CONNECTION from information_schema.views where TABLE_SCHEMA=:database order by TABLE_NAME;';
        $list = $this->query($sqlStr, ['database' => $db_name]);
        if ($with_key_name && $list) {
            $list = $this->setArrayKeyByName($list, 'TABLE_NAME');
        }
        return $list;
    }
    /**
     * 返回所有数据库函数
     */
    public function getFunctions($db_name = '', $with_key_name = true)
    {
        return $this->getRoutines($db_name, 'FUNCTION', $with_key_name);
    }
    /**
     * 返回所有数据库存储过程
     */
    public function getProcedures($db_name = '', $with_key_name = true)
    {
        return $this->getRoutines($db_name, 'PROCEDURE', $with_key_name);
    }
    /**
     * 返回所有程序
     * @param string $db_name
     * @param string $type FUNCTION | PROCEDURE
     * @return array ROUTINE_NAME, ROUTINE_DEFINITION, DEFINER, CHARACTER_SET_CLIENT, COLLATION_CONNECTION, 
     *      DATA_TYPE, CREATED, LAST_ALTERED, ROUTINE_COMMENT, ROUTINE_TYPE, CHARACTER_MAXIMUM_LENGTH, 
     *      CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, DATETIME_PRECISION
     */
    public function getRoutines($db_name = '', $type = '', $with_key_name = true)
    {
        $db_name = $this->checkDbName($db_name);
        if ($type) {
            $type = strtoupper($type);
        }
        $field = 'ROUTINE_NAME, ROUTINE_DEFINITION, DEFINER, CHARACTER_SET_CLIENT, COLLATION_CONNECTION, DATA_TYPE, CREATED, LAST_ALTERED, ROUTINE_COMMENT';
        $sqlStr = 'select ' . $field . ' from information_schema.routines WHERE ROUTINE_SCHEMA=:database'
            . ($type ? " and ROUTINE_TYPE='$type'" : '') . ' order by ROUTINE_TYPE, ROUTINE_NAME;';
        $sqlStr = 'select ' . $field . ' from information_schema.routines limit 10';
        $list = $this->query($sqlStr, ['database' => $db_name]);
        if ($with_key_name && $list) {
            $list = $this->setArrayKeyByName($list, 'ROUTINE_NAME');
        }
        return $list;
    }

    /**
     * 返回程序的所有参数
     * <br />ORDINAL_POSITION=0为返回参数
     * @param string $db_name
     * @param string $routine
     * @return array ORDINAL_POSITION, PARAMETER_MODE, PARAMETER_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
     *          DTD_IDENTIFIER
     */
    public function getRoutineParams($db_name, $routine)
    {
        $db_name = $this->checkDbName($db_name);
        $sqlStr = 'select ORDINAL_POSITION, PARAMETER_MODE, PARAMETER_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, '
            . 'DTD_IDENTIFIER, ROUTINE_TYPE from information_schema.PARAMETERS where SPECIFIC_SCHEMA=:database and '
            . 'SPECIFIC_NAME=:routine';
        $list = $this->query($sqlStr, ['database' => $db_name, 'routine' => $routine]);
        return $list;
    }
    /**
     * 返回程序的所有参数定义
     * @param string $db_name
     * @param string $routine
     * @return array
     */
    public function getRoutineParamsSimple($db_name, $routine)
    {
        $db_name = $this->checkDbName($db_name);
        $list = $this->getRoutineParams($db_name, $routine);
        $outlist = [];
        foreach ($list as $row) {
            $objname = $row['PARAMETER_NAME'];
            $datatype = $row['DATA_TYPE'];

            if ($objname) {
                $outlist[] = $row['PARAMETER_MODE'] . ' ' . $objname . ' '
                    . ($datatype == 'varchar' ? $row['DTD_IDENTIFIER'] : $datatype);
            } else {
                $outlist[] = 'returns: ' . ($datatype == 'varchar' ? $row['DTD_IDENTIFIER'] : $datatype);
            }
        }
        return $outlist;
    }

    /**
     * 获取全文搜索的命令
     */
    public function getFullTextSearchSqls($word, $table_name)
    {
        if (!$word || !$table_name) {
            return [];
        }
        $allowTypes = ['tinyint', 'smallint', 'mediumint', 'int', 'bigint', 'float', 'double', 'decimal', 'char', 'varchar'];
        $charTypes = ['char', 'varchar'];
        $isNumType = is_numeric($word);
        $tables = [];
        if ($table_name == 'all') {
            $tableInfos = $this->getTables('');
            foreach ($tableInfos as $row) {
                $tables[] = $row['TABLE_NAME'];
            }
        } else {
            $tables[] = $table_name;
        }
        $where = [];
        $sqls = [];
        foreach ($tables as $table_name) {
            $where = [];
            $columns = $this->getColumns('', $table_name);
            foreach ($columns as $column) {
                $dataType = $column['DATA_TYPE']; // 小写
                if (!in_array($dataType, $allowTypes)) {
                    continue;
                }
                // 关键词是数字，则所有字段类型都查询；关键词是字符串，则只查询字符串类型的字段
                if (!$isNumType && !in_array($dataType, $charTypes)) {
                    continue;
                }
                $where[] = "`{$column['COLUMN_NAME']}` like '%{$word}%'";
            }
            if ($where) {
                $sqls[] = "select * from {$table_name} where " . implode(' or ', $where);
            }
        }
        return $sqls;
    }
    // ============== 链式操作方法 ================
    protected $queryOptions = [];
    public function table($table_name)
    {
        $this->queryOptions['table'] = $table_name;
        return $this;
    }
    public function where($field, $value)
    {
        if (is_string($field)) {
            $this->queryOptions['where'][] = "`$field` = '$value'";
            return $this;
        }
        if (is_array($field)) {
            $where = [];
            foreach ($field as $row) {
                $where[] = "`{$row[0]}` {$row[1]} '$row[2]'";
            }
            $this->queryOptions['where'] = isset($this->queryOptions['where']) ? array_merge($this->queryOptions['where'], $where) : $where;
            return $this;
        }
        return $this;
    }
    private function parseWhereCondition()
    {
        if (empty($this->queryOptions['where'])) {
            return '';
        }
        return ' where ' . implode(' and ', $this->queryOptions['where']);
    }
    /**
     * 查询数据表记录数量
     * @param string $table_name
     */
    public function count($table_name = '')
    {
        if (!$table_name && isset($this->queryOptions['table'])) {
            $table_name = $this->queryOptions['table'];
        }
        $value = $this->aggFn('count(*) as n');
        return $value ? intval($value) : 0;
    }
    public function max($field)
    {
        $value = $this->aggFn("max({$field}) as n");
        return $value ? doubleval($value) : 0;
    }
    public function min($field)
    {
        $value = $this->aggFn("min({$field}) as n");
        return $value ? doubleval($value) : 0;
    }
    /**
     * 聚合函数基础方法
     */
    public function aggFn($aggExpr, $defVal = null)
    {
        $table = isset($this->queryOptions['table']) ? $this->queryOptions['table'] : '';
        if (!$table) {
            return $defVal;
        }
        $where = $this->parseWhereCondition();
        // 重置 queryOptions 内容
        $this->queryOptions = [];
        $sql = "select {$aggExpr} from {$table} {$where}";
        return $this->value($sql);
    }
    public function update(array $data)
    {
        $table = isset($this->queryOptions['table']) ? $this->queryOptions['table'] : '';
        if (!$table || !$data) {
            return false;
        }
        $where = $this->parseWhereCondition();
        // 重置 queryOptions 内容
        $this->queryOptions = [];
        $sql = '';
        foreach ($data as $k => $v) {
            $sql .= ",`{$k}`='${$v}'";
        }
        $sql = substr($sql, 1);
        $sql = "update {$table} set {$sql} {$where}";
        return $this->execute($sql);
    }
    /**
     * 插入数据
     */
    public function insert($data)
    {
        $table = isset($this->queryOptions['table']) ? $this->queryOptions['table'] : '';
        if (!$table || !$data) {
            return false;
        }
        // 重置 queryOptions 内容
        $this->queryOptions = [];
        $fields = implode('`, `', array_keys($data));
        $values = implode("', '", array_values($data));
        $sql = "insert into {$table} (`{$fields}`) values ('{$values}')";
        return $this->execute($sql);
    }
    public function insertGetId($data)
    {
        if (false === $this->insert($data)) {
            return false;
        }
        return $this->link->lastInsertId();
    }
    public function insertAll(array $data)
    {
        $table = isset($this->queryOptions['table']) ? $this->queryOptions['table'] : '';
        if (!$table) {
            return false;
        }
        $sqls = [];
        foreach ($data as $row) {
            $fields = implode("`, `", array_keys($row));
            $values = implode("', '", array_values($row));
            $sqls[] = "insert into {$table} (`{$fields}`) values ('{$values}')";
        }
        return $this->batchQuery($sqls);
    }

    /**
     * 加载 sql 文件为分号分割的数组，注意最后一条命令语句要以分号结尾，否则最后一条命令不会解析出来
     * 支持存储过程和函数提取，自动过滤注释
     * @param array $lines 文件内容数组
     * @param string $routineSplitor 存储过程分隔符
     * @param boolean $isCompact 是否紧凑模式
     * @return boolean|array
     * @version 1.0 2015-5-27 Added.
     * @version 1.1 2016-9-6 修正 drop function 或 drop procedure 无法解析的问题
     */
    static public function parseSqlStatement($lines, $routineSplitor = ';;', $isCompact = false)
    {
        // $lines = file($path, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES); // 读取文件到数组
        $outlines = [];
        $lines = array_filter($lines);
        if (!$lines) {
            return $outlines;
        }
        $stmt = '';
        $isRoutine = false;
        $routineSplitorEnd = $routineSplitor[0];
        $isSkip = false; // 是否过滤批量注释
        foreach ($lines as $line) {
            $line = trim($line); // 过滤头尾空格
            if (!$line) {
                continue; // 过滤空行
            }
            if (0 === strpos($line, '/*')) { // 批量注释开始
                // 判断是否单行注释 /* ... */
                if (false === strpos($line, '*/')) {
                    $isSkip = true; // 批量注释开始
                }
                continue; // 单行注释，直接跳过
            }
            if ($isSkip && (false !== strpos($line, '*/'))) { // 批量注释结束
                $isSkip = false;
                continue;
            }
            if ($isSkip) {
                continue; // 过滤批量注释
            }
            if (0 === strpos($line, '-- ')) {
                continue; // 过滤单行注释
            }
            $lowerline = strtolower($line);
            $lowerline = preg_replace('/[\s]+/', ' ', trim($lowerline)); // 多个空格合并成一个
            if($isCompact){
                // 紧凑模式
                if(strpos($line,'drop table')===0){
                    continue;
                }
            }
            // 提取存储过程和函数
            if (!$isRoutine && 0 === strpos($lowerline, 'delimiter ' . $routineSplitor)) {
                $isRoutine = true;
                continue;
            }
            if ($isRoutine) {
                // drop 语句独立一行
                if (0 === strpos($lowerline, 'drop function ') || 0 === strpos($lowerline, 'drop procedure ')) {
                    $outlines[] = $line;
                    $stmt = '';
                    continue;
                }
                if (0 === strpos($lowerline, 'delimiter ' . $routineSplitorEnd)) {
                    $isRoutine = false;
                    $outlines[] = $stmt;
                    $stmt = '';
                    continue;
                }
                $stmt .= $line . ' ';
                continue;
            }
            // 提取普通语句，支持一个语句拆分成很多行的写法，注意语句结束后必须以分号结尾
            $stmt .= $line . ' '; // 串联行内容要加个空格，防止 select a\rfrom table，拼凑成 select afrom table
            if (false !== strpos($line, ';') && strlen($line) == (strpos($line, ';') + 1)) {
                $outlines[] = $stmt;
                $stmt = '';
            }
        }
        // 兼容单sql语句，且没有以分号结尾的情况
        // if (!$outlines && $stmt) { // 这样写最后一条命令如果没有分号结尾会解析不出来
        if ($stmt) {
            $outlines[] = $stmt;
        }
        return $outlines;
    }

    /**
     * 从PHP文件转换到SQL
     * 遍历当前文件同级的所有目录下的所有PHP文件。
     * @param string $srcDir '../src/'
     * @param string $logFile '../data.sql'
     * @param boolean $isEcho false
     * @return array
     */
    static public function php2sql($srcDir, $logFile = '', $isEcho = false)
    {
        $isLog = false;
        if ($logFile) {
            $isLog = true;
            file_put_contents($logFile, '');
        }
        $sqlArr = [];
        $sql = '';
        $fsi = new FilesystemIterator($srcDir);
        foreach ($fsi as $fileInfo) {
            if (in_array($fileInfo->getFilename(), ['.', '..'])) {
                continue;
            }
            if (0 === strpos($fileInfo->getFilename(), '_')) {
                // 文件名称或目录名称以下划线开头则当成草稿文件跳过。
                continue;
            }
            if ($fileInfo->isDir()) {
                $fsi2 = new FilesystemIterator($srcDir . $fileInfo->getFilename());
                foreach ($fsi2 as $fileInfo2) {
                    if (0 === strpos($fileInfo2->getFilename(), '_')) {
                        // 文件名称或目录名称以下划线开头则当成草稿文件跳过。
                        continue;
                    }
                    $filename = $fileInfo2->getFilename();
                    if (strpos($filename, '.php')) {
                        $config = require $fileInfo2->getRealPath();
                        if (isset($config['enable']) && !$config['enable']) {
                            unset($config);
                            // 数据中包含 enable = false 开关选项，跳过。
                            continue;
                        }
                        $list = $config['data'];
                        $append = $config['append'] ?? false;
                        $isTruncate = $config['truncate'] ?? false;
                        $isTree = $config['is_tree'] ?? false;
                        $startId = $config['start_id'] ?? 0;
                        if ($isEcho) {
                            echo '处理：', ($config['text'] ?? $filename), ' [', $fileInfo2->getPath(), ']', PHP_EOL;
                        }
                        if ($isLog) {
                            file_put_contents($sqlfile, '-- ' . ($config['text'] ?? $filename) . PHP_EOL, FILE_APPEND);
                        }
                        if ($isTruncate) {
                            $sql = 'truncate table ' . ($config['table'] ?? '{table}') . ';';
                            $sqlArr[] = $sql;
                            if ($isLog) {
                                file_put_contents($sqlfile, ($sql . PHP_EOL), FILE_APPEND);
                            }
                        }
                        if ($isTree) {
                            $id = $startId;
                            $pid = 0;
                            $pid2 = 0;
                            $pid3 = 0;
                            $sqlValues = [];
                            $idArr = [];
                            foreach ($list as $row) {
                                $id++;
                                $idArr[] = $id;
                                $sqlValues = $row;
                                $sqlValues['id'] = $id;
                                $sqlValues['pid'] = $pid;
                                if (isset($sqlValues['children'])) {
                                    unset($sqlValues['children']);
                                }
                                if ($append) {
                                    $sqlValues = array_merge($sqlValues, $append);
                                }
                                $sql = 'insert into `' . $config['table'] . '` (`' . implode('`, `', array_keys($sqlValues)) . '`) values (\'' . implode('\', \'', array_values($sqlValues)) . '\');';
                                $sqlArr[] = $sql;
                                if ($isLog) {
                                    file_put_contents($sqlfile, $sql . PHP_EOL, FILE_APPEND);
                                }
                                if (!isset($row['children'])) {
                                    continue;
                                }
                                $pid2 = $id;
                                foreach ($row['children'] as $row2) {
                                    $id++;
                                    $idArr[] = $id;
                                    $sqlValues = $row2;
                                    $sqlValues['id'] = $id;
                                    $sqlValues['pid'] = $pid2;
                                    if (isset($sqlValues['children'])) {
                                        unset($sqlValues['children']);
                                    }
                                    if ($append) {
                                        $sqlValues = array_merge($sqlValues, $append);
                                    }
                                    $sql = 'insert into `' . $config['table'] . '` (`' . implode('`, `', array_keys($sqlValues)) . '`) values (\'' . implode('\', \'', array_values($sqlValues)) . '\');';
                                    $sqlArr[] = $sql;
                                    if ($isLog) {
                                        file_put_contents($sqlfile, $sql . PHP_EOL, FILE_APPEND);
                                    }
                                    if (!isset($row2['children'])) {
                                        continue;
                                    }
                                    $pid3 = $id;
                                    foreach ($row2['children'] as $row3) {
                                        $id++;
                                        $idArr[] = $id;
                                        $sqlValues = $row3;
                                        $sqlValues['id'] = $id;
                                        $sqlValues['pid'] = $pid3;
                                        if (isset($sqlValues['children'])) {
                                            unset($sqlValues['children']);
                                        }
                                        if ($append) {
                                            $sqlValues = array_merge($sqlValues, $append);
                                        }
                                        $sql = 'insert into `' . $config['table'] . '` (`' . implode('`, `', array_keys($sqlValues)) . '`) values (\'' . implode('\', \'', array_values($sqlValues)) . '\');';
                                        $sqlArr[] = $sql;
                                        if ($isLog) {
                                            file_put_contents($sqlfile, $sql . PHP_EOL, FILE_APPEND);
                                        }
                                    }
                                }
                            }
                            if (isset($config['update_sql'])) {
                                $sql = trim(str_replace('__ids__', implode(',', $idArr), $config['update_sql']));
                                $sqlArr[] = $sql;
                                if ($isLog) {
                                    file_put_contents($sqlfile, (rtrim($sql, ';') . ';' . PHP_EOL), FILE_APPEND);
                                }
                            }
                            unset($idArr);
                            unset($sqlValues);
                        } else {
                            foreach ($list as $row) {
                                if ($append) {
                                    $row = array_merge($row, $append);
                                }
                                $sql = 'insert into `' . $config['table'] . '` (`' . implode('`, `', array_keys($row)) . '`) values (\'' . implode('\', \'', array_values($row)) . '\');';
                                $sqlArr[] = $sql;
                                if ($isLog) {
                                    file_put_contents($sqlfile, $sql . PHP_EOL, FILE_APPEND);
                                }
                            }
                        }
                        if (isset($data)) {
                            // 释放引用文件中的 $data 变量
                            // echo 'unset data.';
                            unset($data);
                        }
                        unset($list);
                        unset($config);
                    }
                }
            }
        }
        return $sqlArr;
    }
}
